// 自行车产品模型 - PostgreSQL
import { postgresPool } from '../../config/multi-database.js';

class BikeProduct {
  // 创建产品
  static async create(productData) {
    const client = await postgresPool.connect();
    
    try {
      const query = `
        INSERT INTO bike_products (
          name, description, category_id, brand, model, 
          frame_size, wheel_size, material, color, 
          price, wholesale_price, stock_quantity, 
          min_order_quantity, status, specifications
        ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15)
        RETURNING *
      `;
      
      const values = [
        productData.name,
        productData.description,
        productData.category_id,
        productData.brand,
        productData.model,
        productData.frame_size,
        productData.wheel_size,
        productData.material,
        productData.color,
        productData.price,
        productData.wholesale_price,
        productData.stock_quantity,
        productData.min_order_quantity,
        productData.status || 'active',
        JSON.stringify(productData.specifications || {})
      ];
      
      const result = await client.query(query, values);
      return result.rows[0];
      
    } finally {
      client.release();
    }
  }
  
  // 根据ID获取产品（带缓存）
  static async findById(id, useCache = true) {
    const { getCache, setCache, CACHE_PREFIX } = await import('../config/multi-database.js');
    
    // 检查缓存
    if (useCache) {
      const cached = await getCache(`${CACHE_PREFIX.PRODUCT}${id}`);
      if (cached) {
        console.log('✅ 从缓存获取产品数据');
        return cached;
      }
    }
    
    const client = await postgresPool.connect();
    
    try {
      const query = `
        SELECT 
          p.*,
          c.name as category_name,
          b.name as brand_name
        FROM bike_products p
        LEFT JOIN product_categories c ON p.category_id = c.id
        LEFT JOIN brands b ON p.brand = b.name
        WHERE p.id = $1 AND p.status = 'active'
      `;
      
      const result = await client.query(query, [id]);
      
      if (result.rows.length === 0) {
        return null;
      }
      
      const product = result.rows[0];
      
      // 缓存产品数据（1小时）
      if (useCache) {
        await setCache(`${CACHE_PREFIX.PRODUCT}${id}`, product, 3600);
      }
      
      return product;
      
    } finally {
      client.release();
    }
  }
  
  // 搜索产品
  static async search(filters = {}, page = 1, limit = 20) {
    const client = await postgresPool.connect();
    
    try {
      let whereConditions = ["p.status = 'active'"];
      let values = [];
      let paramCount = 0;
      
      // 构建查询条件
      if (filters.category_id) {
        paramCount++;
        whereConditions.push(`p.category_id = $${paramCount}`);
        values.push(filters.category_id);
      }
      
      if (filters.brand) {
        paramCount++;
        whereConditions.push(`p.brand = $${paramCount}`);
        values.push(filters.brand);
      }
      
      if (filters.min_price) {
        paramCount++;
        whereConditions.push(`p.wholesale_price >= $${paramCount}`);
        values.push(filters.min_price);
      }
      
      if (filters.max_price) {
        paramCount++;
        whereConditions.push(`p.wholesale_price <= $${paramCount}`);
        values.push(filters.max_price);
      }
      
      if (filters.keyword) {
        paramCount++;
        whereConditions.push(`(
          p.name ILIKE $${paramCount} OR 
          p.description ILIKE $${paramCount} OR
          p.model ILIKE $${paramCount}
        )`);
        values.push(`%${filters.keyword}%`);
      }
      
      const whereClause = whereConditions.length > 0 ? 
        `WHERE ${whereConditions.join(' AND ')}` : '';
      
      // 分页参数
      paramCount++;
      values.push(limit);
      paramCount++;
      values.push((page - 1) * limit);
      
      const query = `
        SELECT 
          p.*,
          c.name as category_name
        FROM bike_products p
        LEFT JOIN product_categories c ON p.category_id = c.id
        ${whereClause}
        ORDER BY p.created_at DESC
        LIMIT $${paramCount - 1} OFFSET $${paramCount}
      `;
      
      const countQuery = `
        SELECT COUNT(*) as total
        FROM bike_products p
        ${whereClause}
      `;
      
      const [productsResult, countResult] = await Promise.all([
        client.query(query, values),
        client.query(countQuery, values.slice(0, -2))
      ]);
      
      return {
        products: productsResult.rows,
        total: parseInt(countResult.rows[0].total),
        page,
        limit,
        totalPages: Math.ceil(parseInt(countResult.rows[0].total) / limit)
      };
      
    } finally {
      client.release();
    }
  }
  
  // 更新库存
  static async updateStock(productId, quantityChange) {
    const client = await postgresPool.connect();
    
    try {
      const query = `
        UPDATE bike_products 
        SET stock_quantity = stock_quantity + $1,
            updated_at = NOW()
        WHERE id = $2 AND status = 'active'
        RETURNING stock_quantity
      `;
      
      const result = await client.query(query, [quantityChange, productId]);
      
      if (result.rows.length > 0) {
        // 清理缓存
        const { delCache, CACHE_PREFIX } = await import('../config/multi-database.js');
        await delCache(`${CACHE_PREFIX.PRODUCT}${productId}`);
        
        return result.rows[0].stock_quantity;
      }
      
      return null;
      
    } finally {
      client.release();
    }
  }
  
  // 获取热门产品（带缓存）
  static async getHotProducts(limit = 10) {
    const { getCache, setCache, CACHE_PREFIX } = await import('../config/multi-database.js');
    
    // 检查缓存
    const cached = await getCache(`${CACHE_PREFIX.STATS}hot_products`);
    if (cached) {
      return cached;
    }
    
    const client = await postgresPool.connect();
    
    try {
      const query = `
        SELECT 
          p.*,
          COUNT(o.id) as order_count
        FROM bike_products p
        LEFT JOIN order_items oi ON p.id = oi.product_id
        LEFT JOIN orders o ON oi.order_id = o.id AND o.created_at >= NOW() - INTERVAL '30 days'
        WHERE p.status = 'active'
        GROUP BY p.id
        ORDER BY order_count DESC, p.created_at DESC
        LIMIT $1
      `;
      
      const result = await client.query(query, [limit]);
      
      // 缓存热门产品（4小时）
      await setCache(`${CACHE_PREFIX.STATS}hot_products`, result.rows, 14400);
      
      return result.rows;
      
    } finally {
      client.release();
    }
  }
}

export default BikeProduct;